{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Help Desk - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "List the Company name and the number of calls for those companies with more than 18 calls.\n",
    "\n",
    "```\n",
    "+------------------+----+\n",
    "| Company_name     | cc |\n",
    "+------------------+----+\n",
    "| Gimmick Inc.     | 22 |\n",
    "| Hamming Services | 19 |\n",
    "| High and Co.     | 20 |\n",
    "+------------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>company_name</th>\n",
       "        <th>cc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gimmick Inc.</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hamming Services</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>High and Co.</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gimmick Inc.', 22), ('Hamming Services', 19), ('High and Co.', 20)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Company_name, COUNT(1) cc\n",
    "FROM Issue JOIN Caller ON (Issue.Caller_id=Caller.Caller_id)\n",
    "  JOIN Customer ON (Caller.Company_ref=Customer.Company_ref)\n",
    "GROUP BY Company_name\n",
    "HAVING cc>18"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Find the callers who have never made a call. Show first name and last name\n",
    "\n",
    "```\n",
    "+------------+-----------+\n",
    "| first_name | last_name |\n",
    "+------------+-----------+\n",
    "| David      | Jackson   |\n",
    "| Ethan      | Phillips  |\n",
    "+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>David</td>\n",
       "        <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethan</td>\n",
       "        <td>Phillips</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('David', 'Jackson'), ('Ethan', 'Phillips')]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT First_name, Last_name\n",
    "  FROM Caller LEFT JOIN Issue ON (Caller.Caller_id=Issue.Caller_id)\n",
    "    GROUP BY Caller.Caller_id, First_name, Last_name\n",
    "    HAVING COUNT(Issue.Caller_id)<1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5\n",
    "\n",
    "```\n",
    "+--------------------+------------+-----------+----+\n",
    "| Company_name       | first_name | last_name | nc |\n",
    "+--------------------+------------+-----------+----+\n",
    "| Pitiable Shipping  | Ethan      | McConnell |  4 |\n",
    "| Rajab Group        | Emily      | Cooper    |  4 |\n",
    "| Somebody Logistics | Ethan      | Phillips  |  2 |\n",
    "+--------------------+------------+-----------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>company_name</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>nc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pitiable Shipping</td>\n",
       "        <td>Ethan</td>\n",
       "        <td>McConnell</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rajab Group</td>\n",
       "        <td>Emily</td>\n",
       "        <td>Cooper</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Somebody Logistics</td>\n",
       "        <td>Ethan</td>\n",
       "        <td>Phillips</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Pitiable Shipping', 'Ethan', 'McConnell', 4),\n",
       " ('Rajab Group', 'Emily', 'Cooper', 4),\n",
       " ('Somebody Logistics', 'Ethan', 'Phillips', 2)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Company_name, b.First_name, b.Last_name, COUNT(1) nc\n",
    "FROM Issue JOIN Caller a ON (Issue.Caller_id=a.Caller_id)\n",
    "  JOIN Customer ON (a.Company_ref=Customer.Company_ref)\n",
    "    JOIN Caller b ON (Customer.Contact_id=b.Caller_id)\n",
    "GROUP BY Company_name, b.First_name, b.Last_name\n",
    "HAVING nc<5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "For each shift show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles (The roles are 'Manager', 'Operator', 'Engineer1', 'Engineer2').\n",
    "\n",
    "```\n",
    "+------------+------------+----+\n",
    "| Shift_date | Shift_type | cw |\n",
    "+------------+------------+----+\n",
    "| 2017-08-12 | Early      |  4 |\n",
    "| 2017-08-12 | Late       |  4 |\n",
    "| 2017-08-13 | Early      |  3 |\n",
    "| 2017-08-13 | Late       |  2 |\n",
    "| 2017-08-14 | Early      |  4 |\n",
    "| 2017-08-14 | Late       |  4 |\n",
    "| 2017-08-15 | Early      |  4 |\n",
    "| 2017-08-15 | Late       |  4 |\n",
    "| 2017-08-16 | Early      |  4 |\n",
    "| 2017-08-16 | Late       |  4 |\n",
    "+------------+------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>shift_date</th>\n",
       "        <th>shift_type</th>\n",
       "        <th>cw</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Early</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Late</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('2017-08-12', 'Early', 4),\n",
       " ('2017-08-12', 'Late', 4),\n",
       " ('2017-08-13', 'Early', 3),\n",
       " ('2017-08-13', 'Late', 2),\n",
       " ('2017-08-14', 'Early', 4),\n",
       " ('2017-08-14', 'Late', 4),\n",
       " ('2017-08-15', 'Early', 4),\n",
       " ('2017-08-15', 'Late', 4),\n",
       " ('2017-08-16', 'Early', 4),\n",
       " ('2017-08-16', 'Late', 4)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT agg.Shift_date, agg.Shift_type, COUNT(agg.p) cw FROM\n",
    "(SELECT DISTINCT a.Shift_date, a.Shift_type, a.p FROM\n",
    "  (SELECT Shift_date, Shift_type, Manager p FROM Shift\n",
    "    UNION ALL SELECT Shift_date, Shift_type, Operator p FROM Shift\n",
    "    UNION ALL SELECT Shift_date, Shift_type, Engineer1 p FROM Shift\n",
    "    UNION ALL SELECT Shift_date, Shift_type, Engineer2 p FROM Shift\n",
    "  ) a\n",
    ") AS agg\n",
    "GROUP BY agg.Shift_date, agg.Shift_type"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Caller 'Harry' claims that the operator who took his most recent call was abusive and insulting. Find out who took the call (full name) and when.\n",
    "\n",
    "```\n",
    "+------------+-----------+---------------------+\n",
    "| first_name | last_name | call_date           |\n",
    "+------------+-----------+---------------------+\n",
    "| Emily      | Best      | 2017-08-16 10:25:00 |\n",
    "+------------+-----------+---------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>call_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Emily</td>\n",
       "        <td>Best</td>\n",
       "        <td>2017-08-16 10:25:00.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Emily', 'Best', '2017-08-16 10:25:00.0')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Staff.First_name, Staff.Last_name, Issue.Call_date\n",
    "FROM Caller JOIN Issue ON (Caller.Caller_id=Issue.Caller_id)\n",
    "  JOIN Staff ON (Issue.Taken_by=Staff.Staff_code)\n",
    "WHERE Caller.First_name='Harry'\n",
    "ORDER BY Issue.Call_date DESC\n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
